<?php
/**
 * Created by PhpStorm.
 * User: lixy
 * Date: 2016/1/19
 * Time: 13:52
 */

namespace app\models;

use Yii;
use app\lib\BaseModel;
use app\models\weixin\T_WXMALL_DORACARD;
class BagDao extends BaseModel
{

    //获取产品
    public static function get_GoodsList($start=0,$pagesize=6,$category='0',$order='0',$filter='0',$openid){
        $ishow='and t.ishow=\'Y\' ';
        if (T_WXMALL_DORACARD::get_User_Sale($openid) > 0) {
            $ishow='';
        }
        //加排序
        if($order=='0'){
            $order='';
        }else{
            //1正序
            $desc="";
            if($order=='2'){
                $desc="desc";
            }
            $order=" order by price $desc ";
        }

        if($category=='0'){
            $categorySQL='';
        }else{
            $categorySQL=" and goods_cl=:category ";
        }

        if($filter=='0'){
            $filterSQL='';
        }else{
            $filterSQL=" and (goods_name like :filter) ";
        }



        $sql="select t.* from t_wxmall_goods t  where t.dr=0 $ishow $categorySQL $filterSQL $order";

//        var_dump($sql);
//        exit;
        $cmd= static::query()->createCommand(static::pageSql($sql,$start,$pagesize));
        if($category!='0') {
            $cmd->bindValue(':category',$category);
        }

        if($filter!='0') {
            $filter='%'.$filter.'%';
            $cmd->bindValue(':filter',$filter);
        }

        return $cmd->queryAll();

    }
//获取产品分类
    public static function get_Category($openid){
        $ishow='and ishow=\'Y\' ';
        if (T_WXMALL_DORACARD::get_User_Sale($openid) > 0) {
            $ishow='';
        }
        $cmd= static::query()->createCommand("select * from t_wxmall_goods_category where 1=1 $ishow");
        return $cmd->queryAll();
    }

    //获取单个产品
    public static function get_Goods($goods_code){
        $cmd= static::query()->createCommand('select * from t_wxmall_goods where goods_code=:goods_code ');
        $cmd->bindValue(':goods_code',$goods_code);
        $goods= $cmd->queryAll();
        if(count($goods)>0){
            $goods=$goods[0];
            $cmd2= static::query()->createCommand('select GOODS_EXHI_URL from T_WXMALL_GOODS_IMAGEINFO where imgtype=1 and GOODS_CODE=:goods_code ');
            $cmd2->bindValue(':goods_code',$goods_code);
            $imgList=$cmd2->queryAll();
            return ['goods'=>$goods,'imgList'=>$imgList];
        }else{
            return false;
        }

    }
    //添加商品到购物车  --ps：界面没有选个数的，所以添加的个数都是1
    public static function add_dorabag_cart($userid,$goods_code){
        $result=0;
        //龙珠不允许加入购物车
        $goodsinfo=static::get_Goods($goods_code);
        if($goodsinfo==false){
            return "没有这个产品";
        }
        if($goodsinfo['goods']['GOODS_CL']=='朵拉活动虚拟产品'){//七龙珠
            return "这个产品不能加入购物车，请去商城选择其他商品!";
        }
        if(static::isExistGoods_dorabag_cart($userid,$goods_code)>0){
            $result= static::inc_dorabag_cart_num($userid,$goods_code,1);
        }else {
            $cmd = static::query()->createCommand('insert into t_wxmall_dorabag_cart (id,user_id,goods_code,num,ischeck) values (:id,:user_id,:goods_code,:num,1) ');
            $cmd->bindValue(':id', static::uuid());
            $cmd->bindValue(':user_id', $userid);
            $cmd->bindValue(':goods_code', $goods_code);
            $cmd->bindValue(':num', 1);
            $result= $cmd->execute();
        }
        if($result>0){
            return 1;
        }
    }
    //判断是否已经添加到购物车了
    public static function isExistGoods_dorabag_cart($userid,$goods_code){
        $cmd= static::query()->createCommand('select count(1) from t_wxmall_dorabag_cart where user_id=:user_id and goods_code=:goods_code');
        $cmd->bindValue(':user_id',$userid);
        $cmd->bindValue(':goods_code',$goods_code);
        return $cmd->queryScalar();
    }
    //增加或者减少购物车商品购买数量
    public static function inc_dorabag_cart_num($userid,$goods_code,$addnum){
        $cmd= static::query()->createCommand('select num from t_wxmall_dorabag_cart  where user_id=:user_id and goods_code=:goods_code');
        $cmd->bindValue(':user_id',$userid);
        $cmd->bindValue(':goods_code',$goods_code);
        $re=$cmd->queryScalar();
        if($re==false){
            return 0;
        }
        if($re <=1 && $addnum<0){
            return 0;
        }

        $cmd= static::query()->createCommand('update t_wxmall_dorabag_cart set num=num+:addnum where user_id=:user_id and goods_code=:goods_code');
        $cmd->bindValue(':user_id',$userid);
        $cmd->bindValue(':goods_code',$goods_code);
        $cmd->bindValue(':addnum',$addnum);
        return $cmd->execute();
    }
    //显示购物车内容
    public static function get_shopping_cartList($userid){
        $cmd= static::query()->createCommand('select c.*,g.goods_name,g.GOODS_IMG_URL,g.PRICE from t_wxmall_dorabag_cart c,t_wxmall_goods g
             where c.goods_code=g.goods_code and c.user_id=:user_id');
        $cmd->bindValue(':user_id',$userid);
        return $cmd->queryAll();
    }
    //删除购物车某产品
    public static function delete_dorabag_cart($userid,$goods_code){
        $cmd= static::query()->createCommand('delete from t_wxmall_dorabag_cart where user_id=:user_id and goods_code=:goods_code');
        $cmd->bindValue(':user_id',$userid);
        $cmd->bindValue(':goods_code',$goods_code);
        return $cmd->execute();
    }
    //购物车选择产品 for 产生订单  ps:至少要有一个
  /*  public static  function create_order_goods_select($USERID,$goodsCodeList){
        $tmp=[];
        foreach($goodsCodeList as &$goodsCode){
            $tmpstr='\''.$goodsCode.'\'';
            $tmp[]=$tmpstr;
        }
        $cmd=  static::query()->createCommand('update t_wxmall_dorabag_cart set ischeck=1 where user_id=:user_id and goods_code in (:tmp)');
        $cmd->bindValue(":tmp",implode(',',$tmp));
        return $cmd->execute();
    }*/

    //计算订单总价
    public static function calc_dorabag_cart_order_TOTALPRICE($userid){
        $cmd= static::query()->createCommand('select nvl(sum(g.price * c.num),0) from t_wxmall_dorabag_cart c,t_wxmall_goods g
          where c.goods_code=g.goods_code and  c.user_id=:user_id and c.ischeck=1');
        $cmd->bindValue(':user_id',$userid);
        return $cmd->queryScalar();
    }
    //计算购物产品总数量
    public static function calc_dorabag_cart_order_TOTALNUM($userid){
        $cmd= static::query()->createCommand('select nvl(sum(num),0) from t_wxmall_dorabag_cart
          where   user_id=:user_id and ischeck=1');
        $cmd->bindValue(':user_id',$userid);
        return $cmd->queryScalar();
    }


    //提交订单
/*PROCEDURE P_WXMALL_CREATE_DORABAG_ORDER
(
USERID IN VARCHAR2              --用户id
, TOTALPRICE IN NUMBER          --订单金额
, RESULTS OUT VARCHAR2            --返回值
)
0不成功，否则返回订单编号*/

    public static function P_WXMALL_CREATE_DORABAG_ORDER($USERID,$TOTALPRICE){

        $cmd=  static::query()->createCommand('call P_WXMALL_CREATE_DORABAG_ORDER( :USERID,:TOTALPRICE,:RESULTS)');
        $cmd->bindValue(':USERID',$USERID);
        $cmd->bindValue(':TOTALPRICE',$TOTALPRICE);
        $cmd->bindParam(':RESULTS', $re,\PDO::PARAM_STR,50);
        $cmd->execute();

        return $re;
    }

    //根据订单编号查询订单
    public static function getOrderInfoByOrderCode($ORDERCODE,$user_id){
        $cmd= static::query()->createCommand('select * from t_wxmall_dorabag_order where user_id=:user_id and code=:code');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->queryAll();
    }
    //根据订单编号查询订单详情
    public static function getOrderGoodsListByOrderCode($ORDERCODE){
        $cmd= static::query()->createCommand('
            select * from t_wxmall_goods g,
            (
            select goods_id,count(1) as num from t_wxmall_dorabag_order_goods
            where bag_order_id=:code    group by goods_id
            ) og
            where  og.goods_id=g.goods_code
        ');
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->queryAll();
    }
    //支付成功
    public static function pay_dorabag_cart_order_TEST($ORDERCODE,$user_id){
//        $re = T_WX_PAY_LOG::P_WXMALL_PAY_DORABAG_ORDER($data['result_code'], $err_code_des
//            , $data['openid'], $data['total_fee'], $order_code, $data['time_end']
//            , $attach);
        $cmd= static::query()->createCommand('update  t_wxmall_dorabag_order set status=2 where CODE=:code and user_id=:user_id ');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        return $cmd->execute();
    }
    //添加订单的祝福语
    public static function add_dorabag_cart_order_BLESSING($ORDERCODE,$user_id,$BLESSING){
        $cmd= static::query()->createCommand('update  t_wxmall_dorabag_order set BLESSING=:BLESSING where CODE=:code and user_id=:user_id ');
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':code',$ORDERCODE);
        $cmd->bindValue(':BLESSING',$BLESSING);
        return $cmd->execute();
    }





    //礼包内容
    public static function get_bagInfo($code){
        $cmd= static::query()->createCommand('select o.*,u.nick_name,u.header_url from  t_wxmall_dorabag_order o,t_wxmall_user u
         where o.code=:code and u.id=o.user_id');
        $cmd->bindValue(':code',$code);
        return $cmd->queryAll();
    }
    //    礼品包产品总数  bag_order_id
    public static function get_bagGoodsCount($code){
        $cmd= static::query()->createCommand('select count(1) from t_wxmall_dorabag_order_goods where bag_order_id=:bag_order_id');
        $cmd->bindValue(':bag_order_id',$code);
        return $cmd->queryScalar();
    }
    //已领取的礼物数量
    public static function get_bagGoods_receive_Count($code){
        $cmd= static::query()->createCommand('select count(1) from t_wxmall_dorabag_order_goods where bag_order_id=:bag_order_id  and status!=0');
        $cmd->bindValue(':bag_order_id',$code);
        return $cmd->queryScalar();
    }
    //订单领取记录
    public static function get_bagGoods_receive_infoList($code){
        $cmd= static::query()->createCommand('select g.*,substr(g.USER_GET_DATETIME,6,5)  SHOW_USER_GET_DATETIME,u.nick_name,u.header_url,s.goods_name
        from  t_wxmall_dorabag_order_goods g,t_wxmall_user u,t_wxmall_goods s
         where g.bag_order_id=:code and g.status!=0 and u.id=g.user_id and s.goods_code=g.goods_id order by g.USER_GET_DATETIME desc');
        $cmd->bindValue(':code',$code);
        return $cmd->queryAll();
    }
    //通过userid和订单code 查询获取的产品信息
    public static function get_bagGoods_receive_GOODSINFO($code,$user_id){
        $cmd= static::query()->createCommand('select g.*,o.id code_id,o.price price_g from t_wxmall_dorabag_order_goods o,t_wxmall_goods  g
            where o.user_id=:user_id and o.bag_order_id=:code and g.goods_code=o.goods_id');
        $cmd->bindValue(':code',$code);
        $cmd->bindValue(':user_id',$user_id);
        return $cmd->queryAll();
    }
    //产品code查询展示图
    public static function get_GOODS_IMG_INFO($code){
        $cmd= static::query()->createCommand('select GOODS_EXHI_URL from T_WXMALL_GOODS_IMAGEINFO
            where IMGTYPE=1 and GOODS_CODE=:code');
        $cmd->bindValue(':code',$code);
        return $cmd->queryAll();
    }

    //通过code_id查询ordergood
    public static function get_GiftInfoByCodeId($code_id,$user_id){
        $cmd= static::query()->createCommand('select *  from t_wxmall_dorabag_order_goods where id=:code_id and user_id=:user_id');
        $cmd->bindValue(':code_id',$code_id);
        $cmd->bindValue(':user_id',$user_id);
        return $cmd->queryAll();
    }

    ////////-----------------------抢礼物
    /*P_RECEIVE_DORABAG
    (
    ORDERID IN VARCHAR2         --订单编号
    , USERID IN VARCHAR2          --用户ID
    , RESULTS OUT VARCHAR2         --返回值
    )
    RESULTS=1 成功  2已经领取过了*/
    //领取礼物/抢礼物
    public static function P_RECEIVE_DORABAG($code,$user_id){
        $cmd=  static::query()->createCommand('call P_RECEIVE_DORABAG(:code,:userid,:RESULTS)');
        $cmd->bindValue(':code',$code);
        $cmd->bindValue(':userid',$user_id);
        $cmd->bindParam(':RESULTS', $RESULTS,\PDO::PARAM_STR,50);
        $cmd->execute();
//        Yii::trace($orderid);
        return $RESULTS;
    }
    //通过code_id+status查询ordergood
    public static function get_GiftInfoByCodeId_status($code_id,$user_id,$status){
        $cmd= static::query()->createCommand('select o.*,g.goods_name,g.goods_cl  from t_wxmall_dorabag_order_goods o,t_wxmall_goods g
        where o.id=:code_id and o.user_id=:user_id and o.status=:status and o.goods_id=g.goods_code');
        $cmd->bindValue(':code_id',$code_id);
        $cmd->bindValue(':user_id',$user_id);
        $cmd->bindValue(':status',$status);
        return $cmd->queryAll();
    }
    //通过ordercode+userid 查询领取的礼物
    public static function get_GiftInfoByOrderCode($order_code,$user_id){
        $cmd= static::query()->createCommand('select *  from t_wxmall_dorabag_order_goods
        where bag_order_id=:order_code and user_id=:user_id ');
        $cmd->bindValue(':order_code',$order_code);
        $cmd->bindValue(':user_id',$user_id);

        return $cmd->queryAll();
    }
    //提交兑换商品的订单
    public static function submit_goods_exchange_order($code_id,$user_id
        ,$receiver_name,$receiver_phone,$receiver_address,$receiver_province
    ,$receiver_city,$receiver_region,$REMARK,$SYNSTATUS){
        $cmd=  static::query()->createCommand('
            update t_wxmall_dorabag_order_goods set
               status=2,SYNSTATUS=:SYNSTATUS
               ,exchange_datetime=to_char(sysdate,\'yyyy-mm-dd hh24:mi:ss\')
               ,receiver_name=:receiver_name
            ,receiver_phone=:receiver_phone
            ,receiver_address=:receiver_address
            ,receiver_province=:receiver_province
            ,receiver_city=:receiver_city
            ,receiver_region=:receiver_region
            ,REMARK=:REMARK
            where id=:code_id and user_id=:user_id and status=1
        ');
        $cmd->bindValue(':code_id',$code_id);
        $cmd->bindValue(':user_id',$user_id);

        $cmd->bindValue(':receiver_name',$receiver_name);
        $cmd->bindValue(':receiver_phone',$receiver_phone);
        $cmd->bindValue(':receiver_address',$receiver_address);
        $cmd->bindValue(':receiver_province',$receiver_province);
        $cmd->bindValue(':receiver_city',$receiver_city);
        $cmd->bindValue(':receiver_region',$receiver_region);
        $cmd->bindValue(':REMARK',$REMARK);
        $cmd->bindValue(':SYNSTATUS',$SYNSTATUS);

        return $cmd->execute();
    }
    //设置订单nc同步状态
/*0：未同步
1：已同步*/
    public static function setNC_SYNSTATUS($code_id,$is_syn){
        $cmd=  static::query()->createCommand('update t_wxmall_dorabag_order_goods set SYNSTATUS=:is_syn where id=:code_id');
        $cmd->bindValue(':is_syn', $is_syn);
        $cmd->bindValue(':code_id', $code_id);
        return $cmd->execute();
    }

    // 获取定制礼包的商品列表
    public static function get_BagGoodsList($bag_id){
        $cmd=  static::query()->createCommand('
            select t0.*, t1.GOODS_IMG_URL, t1.PRICE from T_WXMALL_DORABAG_GOODS t0
            left join T_WXMALL_GOODS t1 on t1.GOODS_ID=t0.GOODS_ID
            WHERE t0.BAG_ID=:bag_id and t0.DR=0');
        $cmd->bindValue(':bag_id',$bag_id);
        return $cmd->queryAll();
    }




/*
P_WXMALL_CUSTOM_DORABAG_ORDER   --定制礼包订单
(
USERID IN VARCHAR2            --用户id
, DORABAGID IN VARCHAR2         --定制礼包编码
, TOTALPRICE IN VARCHAR2        --订单价格
, RESULTS OUT VARCHAR2           --返回值   0失败 其他返回订单号
)*/

    public static function P_WXMALL_CUSTOM_DORABAG_ORDER($user_id,$DORABAGID,$TOTALPRICE){
        $cmd=  static::query()->createCommand('call P_WXMALL_CUSTOM_DORABAG_ORDER(:userid,:DORABAGID,:TOTALPRICE,:RESULTS)');
        $cmd->bindValue(':DORABAGID',$DORABAGID);
        $cmd->bindValue(':userid',$user_id);
        $cmd->bindValue(':TOTALPRICE',$TOTALPRICE);
        $cmd->bindParam(':RESULTS', $RESULTS,\PDO::PARAM_STR,50);
        $cmd->execute();
//        Yii::trace($orderid);
        return $RESULTS;
    }
}